#include "dbmanager.h"

DbManager::DbManager() {
    // 假设你已经成功连接到数据库
    QSqlQuery query;

    this -> init();
}
DbManager::~DbManager(){
    this -> db.close();
}

void DbManager::init(){
    // 创建一个QSqlDatabase对象，并且设置数据库类型为SQLite
    this -> db = QSqlDatabase::addDatabase("QSQLITE");

    // 设置数据库文件路径
    db.setDatabaseName("user.db");


    if(! this -> db.open())
    {
        qDebug() << "Failed to open the database:" << db.lastError().text();
        return;
    }

    // 打开外键约束
    QSqlQuery queryKey;
    if (!queryKey.exec("PRAGMA foreign_keys = ON;")) {
        qDebug() << "Failed to enable foreign keys:" << queryKey.lastError().text();
    } else {
        qDebug() << "Foreign keys enabled successfully.";
    }

    qDebug() << "user.db init and open database success";

    QSqlQuery query;
    QString createUserTableQuery = "CREATE TABLE IF NOT EXISTS "
                                   "users ("
                                   "id INTEGER PRIMARY KEY AUTOINCREMENT , "
                                   "ip TEXT,"
                                   "name TEXT NOT NULL UNIQUE, "
                                   "password TEXT NOT NULL,"
                                   "status INTEGER)";

    if (!query.exec(createUserTableQuery)) {
        qDebug() << "Error creating table:" << query.lastError().text();
    } else {
        qDebug() << "User table checked/created successfully";
    }

    // 创建聊天记录表
    QString createChatHistoryTableQuery = "CREATE TABLE IF NOT EXISTS "
                                          "chat_history ("
                                          "user_one_id INTEGER, "
                                          "user_two_id INTEGER, "
                                          "message TEXT, "
                                          "message_time TEXT, "  // 使用TEXT来存储QTime的字符串表示
                                          "message_type INTEGER, "
                                          "PRIMARY KEY (user_one_id, user_two_id, message_time))";

    if (!query.exec(createChatHistoryTableQuery)) {
        qDebug() << "Error creating chat_history table:" << query.lastError().text();
    } else {
        qDebug() << "ChatHistory table checked/created successfully";
    }

    // 创建 friends 表
    QString createFriendTableQuery = "CREATE TABLE IF NOT EXISTS "
                                     "friends ("
                                     "id INTEGER PRIMARY KEY AUTOINCREMENT, "
                                     "user_id INTEGER, "
                                     "friend_id INTEGER, "
                                     "FOREIGN KEY(user_id) REFERENCES users(id), "
                                     "FOREIGN KEY(friend_id) REFERENCES users(id))";
    if (!query.exec(createFriendTableQuery)) {
        qDebug() << "Error creating friends table:" << query.lastError().text();
    }else{
        qDebug() << "Friend tabel checked/created successfully";
    }

}

QList<User> DbManager::getUserList() {
    QList<User> userList;
    QSqlQuery query("SELECT id, name, password, status FROM users");

    while (query.next()) {
        User u;
        u.id = query.value(0).toInt();
        u.user_name = query.value(1).toString();
        u.user_password = query.value(2).toString();
        u.user_status = static_cast<UserStatus>(query.value(3).toInt());
        userList.append(u);
    }

    return userList;
}

bool DbManager::insertUser(const User &u)
{
    QSqlQuery query;

    // 准备 SQL 插入语句
    query.prepare("INSERT INTO users (id ,name, password, ip, status) VALUES (:id , :name, :password, :ip, :status)");

    // 绑定值
    query.bindValue(":id",u.id);
    query.bindValue(":name", u.user_name);
    query.bindValue(":ip", u.ip);
    query.bindValue(":password", u.user_password);
    query.bindValue(":status", static_cast<int>(u.user_status));

    // 执行查询
    if (!query.exec()) {
        qDebug() << "Error inserting user:" << query.lastError().text();
        return false;
    } else {
        qDebug() << "User inserted successfully";
        return true;
    }
}

bool DbManager::updateUser(const User &u) {
    QSqlQuery query;

    // 准备 SQL 更新语句
    query.prepare("UPDATE users SET name = :name, password = :password, status = :status WHERE id = :id");

    // 绑定值
    query.bindValue(":name", u.user_name);
    query.bindValue(":password", u.user_password);
    query.bindValue(":status", static_cast<int>(u.user_status));
    query.bindValue(":id", u.id);

    // 执行查询
    if (!query.exec()) {
        qDebug() << "Error updating user:" << query.lastError().text();
        return false;
    } else {
        qDebug() << "User updated successfully";
        return true;
    }
}

bool DbManager::insertChatHistory(int sender_id, int receiver_id, MessageType messageType, QString message) {
    QSqlQuery query;

    // 创建 SQL 插入语句
    query.prepare("INSERT INTO chat_history (user_one_id, user_two_id, message, message_time, message_type) "
                  "VALUES (:sender_id, :receiver_id, :message, :message_time, :message_type)");

    // 获取当前时间
    QTime currentTime = QTime::currentTime();

    // 绑定查询参数
    query.bindValue(":sender_id", sender_id);
    query.bindValue(":receiver_id", receiver_id);
    query.bindValue(":message", message);
    query.bindValue(":message_time", currentTime.toString(Qt::ISODate));  // 将时间格式化为 ISO 8601
    query.bindValue(":message_type", static_cast<int>(messageType));

    // 执行插入
    if (!query.exec()) {
        qDebug() << "Error inserting chat history:" << query.lastError().text();
        return false;
    } else {
        qDebug() << "Chat history inserted successfully.";
        return true;
    }
}

bool DbManager::insertFriendRelation(int user_one_id, int user_two_id) {
    QSqlQuery query;

    // 检查是否已经存在相同的好友关系
    query.prepare("SELECT COUNT(*) FROM friends WHERE (user_id = :user_one_id AND friend_id = :user_two_id) "
                  "OR (user_id = :user_two_id AND friend_id = :user_one_id)");
    query.bindValue(":user_one_id", user_one_id);
    query.bindValue(":user_two_id", user_two_id);

    if (!query.exec() || !query.next()) {
        qDebug() << "Error checking existing relations:" << query.lastError().text();
        return false;
    }

    int count = query.value(0).toInt();
    if (count > 0) {
        qDebug() << "Friend relation already exists.";
        return false;  // 关系已存在，拒绝插入
    }

    // 开始事务
    if (!query.exec("BEGIN TRANSACTION")) {
        qDebug() << "Failed to begin transaction:" << query.lastError().text();
        return false;
    }

    // 第一次插入
    query.prepare("INSERT INTO friends (user_id, friend_id) VALUES (:user_id, :friend_id)");
    query.bindValue(":user_id", user_one_id);
    query.bindValue(":friend_id", user_two_id);

    if (!query.exec()) {
        qDebug() << "Error inserting first friend relation:" << query.lastError().text();
        query.exec("ROLLBACK");
        return false;
    }

    // 第二次插入
    query.prepare("INSERT INTO friends (user_id, friend_id) VALUES (:user_id, :friend_id)");
    query.bindValue(":user_id", user_two_id);
    query.bindValue(":friend_id", user_one_id);

    if (!query.exec()) {
        qDebug() << "Error inserting second friend relation:" << query.lastError().text();
        query.exec("ROLLBACK");
        return false;
    }

    // 提交事务
    if (!query.exec("COMMIT")) {
        qDebug() << "Failed to commit transaction:" << query.lastError().text();
        return false;
    }

    qDebug() << "Friend relations inserted successfully.";
    return true;
}


QList<Friend> DbManager::getFriendList(int userId) {
    QList<Friend> friends;
    QSqlQuery query;
    query.prepare("SELECT f.user_id, f.friend_id, u.status FROM friends f "
                  "JOIN users u ON f.friend_id = u.id "
                  "WHERE f.user_id = :user_id");
    query.bindValue(":user_id", userId);

    if (query.exec()) {
        while (query.next()) {
            Friend f;
            f.id = query.value(0).toInt();
            f.friend_id = query.value(1).toInt();
            int status = query.value(2).toInt();
            f.friend_status = static_cast<UserStatus>(status);

            friends.append(f);
        }
    } else {
        qDebug() << "Error retrieving friend list:" << query.lastError().text();
    }

    return friends;
}

QList<ChatHistory> DbManager::getChatHistory(int user_one_id, int user_two_id) {
    QList<ChatHistory> chatHistoryList;
    QSqlQuery query;

    // 准备 SQL 查询语句，获取聊天记录
    query.prepare("SELECT user_one_id, user_two_id, message, message_time, message_type "
                  "FROM chat_history "
                  "WHERE (user_one_id = :user_one_id AND user_two_id = :user_two_id) "
                  "OR (user_one_id = :user_two_id AND user_two_id = :user_one_id) "
                  "ORDER BY message_time");

    // 绑定查询参数
    query.bindValue(":user_one_id", user_one_id);
    query.bindValue(":user_two_id", user_two_id);

    if (query.exec()) {
        // 遍历查询结果
        while (query.next()) {
            ChatHistory chatHistory;
            chatHistory.user_one_id = query.value(0).toInt();
            chatHistory.user_two_id = query.value(1).toInt();
            chatHistory.message = query.value(2).toString();
            chatHistory.message_time = QTime::fromString(query.value(3).toString(), Qt::ISODate);  // 使用ISO格式解析时间
            chatHistory.message_type = static_cast<MessageType>(query.value(4).toInt());

            chatHistoryList.append(chatHistory);
        }
    } else {
        qDebug() << "Error retrieving chat history:" << query.lastError().text();
    }

    return chatHistoryList;
}

User DbManager::getUserInformation(int userId) {
    QSqlQuery query;
    QString queryString = "SELECT id, ip, name, password, status FROM users WHERE id = :userId";
    query.prepare(queryString);
    query.bindValue(":userId", userId);

    if (!query.exec()) {
        qDebug() << "getUserInformation Error executing query:" << query.lastError().text();
        return {};  // 查询失败，返回默认 User 对象
    }

    if (query.next()) {
        User user;
        user.id = query.value("id").toInt();
        user.ip = query.value("ip").toString();
        user.user_name = query.value("name").toString();
        user.user_password = query.value("password").toString();
        user.user_status = static_cast<UserStatus>(query.value("status").toInt());

        return user;  // 返回查询到的 User 对象
    }else{
        User user;
        user.id = -1;
        return user;
    }
}


bool DbManager::deleteFriendRelation(int user_one_id, int user_two_id) {
    QSqlQuery query;

    // 开始事务
    if (!query.exec("BEGIN TRANSACTION")) {
        qDebug() << "Failed to begin transaction:" << query.lastError().text();
        return false;
    }

    // 第一次删除
    query.prepare("DELETE FROM friends WHERE user_id = :user_id AND friend_id = :friend_id");
    query.bindValue(":user_id", user_one_id);
    query.bindValue(":friend_id", user_two_id);

    if (!query.exec()) {
        qDebug() << "Error deleting first friend relation:" << query.lastError().text();
        query.exec("ROLLBACK");
        return false;
    }

    // 第二次删除
    query.prepare("DELETE FROM friends WHERE user_id = :user_id AND friend_id = :friend_id");
    query.bindValue(":user_id", user_two_id);
    query.bindValue(":friend_id", user_one_id);

    if (!query.exec()) {
        qDebug() << "Error deleting second friend relation:" << query.lastError().text();
        query.exec("ROLLBACK");
        return false;
    }

    // 提交事务
    if (!query.exec("COMMIT")) {
        qDebug() << "Failed to commit transaction:" << query.lastError().text();
        return false;
    }

    qDebug() << "Friend relations deleted successfully.";
    return true;
}

QList<Friend> DbManager::getAllFriend(){
    QList<Friend> friends;
    QSqlQuery query;
    query.prepare("SELECT user_id, friend_id FROM friends");

    if (query.exec()) {
        while (query.next()) {
            Friend f;
            f.id = query.value(0).toInt();
            f.friend_id = query.value(1).toInt();
            f.friend_status = UserStatus::ONLINE;

            friends.append(f);
        }
    } else {
        qDebug() << "Error retrieving friend list:" << query.lastError().text();
    }

    return friends;
}
void DbManager::printAllUser(){
    QSqlQuery query;
    query.prepare("SELECT id FROM users");
    if(query.exec())
    {
        while(query.next())
        {
            qDebug() << "用户:" << query.value(0).toInt();
        }
    }
    return;
}
